package com.tool.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
/**
 * 
* @author dinghao
* @ClassName: ReadXls 
* @Description: 读取xls文件
* @date 2018年3月16日 下午2:19:18 
*
 */
public class ReadXls {
	
	private static final Logger log = LoggerFactory.getLogger(ReadXls.class);
	
	 // 去读Excel的方法readExcel，该方法的入口参数为一个File对象  
	 public static List<List<Map<String, String>>> readExcelToMap(File file) throws IOException, BiffException {  
		 InputStream is = null;
		 List<List<Map<String, String>>> result = null;
	     try {  
	    	 result = new ArrayList<List<Map<String,String>>>();//对应excel文件  
	         // 创建输入流，读取Excel  
	         is = new FileInputStream(file.getAbsolutePath());  
	         // jxl提供的Workbook类  
	         Workbook wb = Workbook.getWorkbook(is);  
	         // Excel的页签数量  
	         int sheet_size = wb.getNumberOfSheets();  
	         for (int index = 0; index < sheet_size; index++) { 
	        	 List<Map<String, String>> sheetList = new ArrayList<Map<String, String>>();//对应sheet页  
	             // 每个页签创建一个Sheet对象  
	             Sheet sheet = wb.getSheet(index);  
	             // sheet.getRows()返回该页的总行数  
	             for (int i = 1; i < sheet.getRows(); i++) {  
	                 // sheet.getColumns()返回该页的总列数  
	            	 Map<String,String> map = new HashMap<String,String>();;
	                 for (int j = 0; j < sheet.getColumns(); j++) {  
	                	 String key = sheet.getCell(j, 0).getContents(); 
	                     String value = sheet.getCell(j, i).getContents(); 
	                     if(!key.isEmpty()) {
	                    	 map.put(key, value);
	                     }
	                 }
	                 sheetList.add(map);
	             }  
	             result.add(sheetList);
	             return result;
	         }  
	     }finally {
	    	 if(is != null) {
	  		   	is.close();
	  	   	}
	     }
	     return result;
	 } 
	 
	//将excel文件的每一行数据映射为一个JavaBean对象 
	public static <T> List<T> readExcelToBeanList(String path,Class<T> clazz) throws BiffException, IOException, InstantiationException, IllegalAccessException{
		T newInstance = clazz.newInstance();
		List<T> tList = new ArrayList<T>();
		File file = new File(path);
		try {
			List<List<Map<String, String>>> readExcel = ReadXls.readExcelToMap(file);
			//遍历每一页
			for (List<Map<String, String>> list : readExcel) {
				System.out.println(list.size());
				//遍历每页的每一行
				for (Map<String, String> map : list) {
					try {
						//每一个map代表一个重点人对象
						newInstance = ToBeanUtils.mapToBean(map, clazz);
					} catch (Exception e) {
						log.error(map+"对象映射为Bean对象失败");
						continue;
					}
					tList.add(newInstance);
				}
			}
		} catch (Exception e) {
			log.error("Excel解析失败");
		}
		return tList;
	}
}
